﻿Public Class acceso_a_datos
    Enum motores
        oracle
        sqlserver
        access
    End Enum

    Dim conexion As New Object
    Dim cmd As New Object
    Dim motor As motores = motores.access
    Dim sting_conexion As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\utn\PAV1_VB.NET\clases\tp06_clases\DBF.mdb"

    Dim tabla As String = ""
    Public Property _CadenaConexion() As String
        Get
            Return Me.sting_conexion
        End Get
        Set(ByVal value As String)
            Me.sting_conexion = value
        End Set
    End Property
    Public Property _tabla() As String
        Get
            Return Me.tabla
        End Get
        Set(ByVal value As String)
            Me.tabla = value
        End Set
    End Property
    Public Sub New()
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    Public Sub New(ByVal _CadenaConexion As String, ByVal _MotorBaseDatos As motores)
        Me.sting_conexion = _CadenaConexion
        Me.motor = _MotorBaseDatos

        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    Public Sub New(ByVal _CadenaConexion As String, ByVal _NombreTabla As String, ByVal _MotorBaseDatos As motores)
        Me.sting_conexion = _CadenaConexion
        Me.tabla = _NombreTabla
        Me.motor = _MotorBaseDatos

        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    Public Sub New(ByVal _MotorBaseDatos As motores, ByVal _NombreTabla As String)
        Me.tabla = _NombreTabla
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    Public Sub New(ByVal _MotorBaseDatos As motores)
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub

    Private Sub _conectar()
        Me.conexion.ConnectionString = Me.sting_conexion
        Me.conexion.Open()
        Me.cmd.Connection = conexion
        Me.cmd.CommandType = CommandType.Text
    End Sub
    Public Function leo_tabla() As Data.DataTable
        Return Me._consulta("select * from " & Me.tabla)
    End Function
    Public Function leo_tabla(ByVal pk As String, ByVal descripcion As String) As Data.DataTable
        Return Me._consulta("select " & pk & ", " & descripcion & " from " & Me.tabla)
    End Function
    Public Function leo_tabla(ByVal restriccion As String) As Data.DataTable
        Return Me._consulta("select * from " & Me.tabla & " Where " & restriccion)
    End Function
    Public Function _modicar(ByVal comando As String) As Boolean
        Me._conectar()
        Me.cmd.CommandText = comando
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Function
    Public Function _consulta(ByVal comando As String) As Data.DataTable
        Dim _tabla As New Data.DataTable
        Me._conectar()
        Me.cmd.CommandText = comando
        _tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
        Return _tabla
    End Function
    Public Function _borrar(ByVal comando As String) As Boolean
        Me._conectar()
        Me.cmd.CommandText = comando
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Function
    Public Function _insertar(ByVal valores As String) As Boolean
        Dim txt_Insert As String = ""

        txt_Insert = Me.armo_insert()
        txt_Insert += Me.asigno_valores_insert(valores)

        MsgBox(txt_Insert)

        Me._conectar()
        Me.cmd.CommandText = txt_Insert
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
        Return True
    End Function

    Private Function armo_insert() As String
        Dim tabla As Data.DataTable
        Dim contador As Integer = 0
        Dim txt As String = ""

        tabla = Me.leo_tabla()

        txt = "insert into " & Me.tabla & " ("

        While tabla.Columns.Count() > contador
            txt += tabla.Columns(contador).Caption
            contador += 1
            If contador < tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += ") values ("
        Return txt
    End Function
    Private Function asigno_valores_insert(ByVal txt_insertar As String) As String
        Dim objeto_tabla As Data.DataTable      'tabla que contiene la estructura en la base
        Dim contador As Integer = 0             'contador de bucle
        Dim campo As Integer = 0                'ubicación del campo 
        Dim coma As Integer = 0                 'ubicacion de la "," 
        Dim igual As Integer = 0                'ubicación del "="
        Dim txt_origen As String = ""           'texto origen 
        Dim nombre_campo As String = ""         'campo a buscar 
        Dim valor As String = ""                'valor del campo 
        Dim txt As String = ""                  'texto que se va formando para el insert
        Dim tipo_dato As String = ""

        objeto_tabla = Me.leo_tabla()
        txt_origen = txt_insertar.ToUpper.Trim()

        While objeto_tabla.Columns.Count() > contador

            nombre_campo = objeto_tabla.Columns(contador).Caption.ToUpper.Trim()
            tipo_dato = objeto_tabla.Columns(contador).DataType.ToString

            'MsgBox(nombre_campo & ": " & tipo_dato)

            campo = txt_origen.IndexOf(nombre_campo)
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
                If coma <> -1 Then
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1, coma - igual - 1), tipo_dato)
                Else
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1), tipo_dato)
                End If
            Else
                igual = -1
                coma = -1
                valor = "null"
            End If

 
            txt += valor

            contador += 1
            If contador < objeto_tabla.Columns.Count() Then
                txt += ", "
            End If

        End While

        txt += ")"
        Return txt

    End Function
    Private Function acomodo_al_tipo_dato(ByVal texto As String, ByVal tipo_dato As String) As String
        Select Case tipo_dato
            Case "System.String"
                Return "'" & texto & "'"
            Case "System.Int16"
                Return texto
            Case "System.Int32"
                Return texto
            Case "System.Int64"
                Return texto
            Case "System.Byte"
                Return texto
            Case "System.Double"
                Return texto
            Case "System.Decimal"
                Return texto
            Case "System.DateTime"
                If motor = motores.access Then
                    Return "#" & texto & "#"
                Else
                    Return "'" & texto & "'"
                End If
            Case "System.DateTimeKind"
                If motor = motores.access Then
                    Return "#" & texto & "#"
                Else
                    Return "'" & texto & "'"
                End If
            Case Else
                Return texto
        End Select
    End Function
End Class
